﻿Public Class clsDbLotDefect

    Public Function GetAsmDefectData(ByVal szLotNo As String, Optional ByVal szEquipID As String = "") As DataTable
        Dim szSql As String = ""
        szSql &= "SELECT DEF.CAT_CD as コード, DEF.CAT_NAME as 不良内容, COUNT('X') as 数量 "
        szSql &= "FROM TRA.L_LOT_DEFECT DEF "
        szSql &= "INNER JOIN TRA.L_FRAME_ADDR ADR "
        szSql &= "ON ADR.LOT_NO=DEF.LOT_NO "
        szSql &= "AND ADR.RACK_NO=DEF.RACK_NO "
        szSql &= "AND DANSU <= ADR.FRAME_CNT "
        szSql &= "AND TO_NUMBER(DEF.MAP_X,'999') <= ADR.FRM_COL_CNT "
        szSql &= "AND TO_NUMBER(DEF.MAP_Y,'999') <= ADR.FRM_ROW_CNT "
        szSql &= "WHERE DEF.LOT_NO = '" & szLotNo & "' "
        szSql &= "AND DEF.KOUTEI_CD <> 'M050' "
        If Not String.IsNullOrEmpty(szEquipID) Then
            szSql &= "AND DEF.EQUIP_ID = '" & szEquipID & "' "
        End If
        szSql &= "GROUP BY DEF.CAT_CD, DEF.CAT_NAME "
        szSql &= "ORDER BY DEF.CAT_CD "

        Dim dt As DataTable = DBacs.ExecuteSql(szSql, -1, szLotNo)
        If IsNothing(dt) Then
            GetAsmDefectData = New DataTable
        Else
            GetAsmDefectData = dt
        End If
    End Function

    Public Function GetDefectData(ByVal szLotNo As String, ByVal szEquipID As String, ByVal szProcCD As String) As DataTable
        Dim szSql As String = ""
        '>> 2018/02/19 安部 検査数量タイプ 追加
        '>> 2018/02/22 安部 検査数量タイプ 削除 ※共通部で不要なカラムが出力されるため別途単独SQLで取得する
        'szSql &= "SELECT CAT_CD as コード, CAT_NAME as 不良内容, CAT_CNT as 数量 "
        'szSql &= "SELECT CAT_CD as コード, CAT_NAME as 不良内容, CAT_CNT as 数量, Inspection_Qty_Type as 検査数量タイプ "
        szSql &= "SELECT CAT_CD as コード, CAT_NAME as 不良内容, CAT_CNT as 数量 "
        '>> 2018/02/22 安部 検査数量タイプ 削除 ※共通部で不要なカラムが出力されるため別途単独SQLで取得する
        szSql &= "  FROM TRA.L_LOT_DEFECT "
        'szSql &= "WHERE LOT_NO = '" & szLotNo & "' "
        'szSql &= "AND EQUIP_ID = '" & szEquipID & "' "
        'szSql &= "AND KOUTEI_CD = '" & szProcCD & "' "
        szSql &= "WHERE LOT_NO = ? "
        szSql &= "AND EQUIP_ID = ? "
        szSql &= "AND KOUTEI_CD = ? "

        'Dim dt As DataTable = DBacs.ExecuteSql(szSql, -1, szLotNo)
        Dim dt As DataTable = DBacs.ExecuteSql(szSql, -1, szLotNo, szEquipID, szProcCD)
        '<< 2018/02/19 安部 検査数量タイプ 追加
        If IsNothing(dt) Then
            GetDefectData = New DataTable
        Else
            GetDefectData = dt
        End If
    End Function

    Public Function GetInspection_Qty_Type(ByVal szLotNo As String, ByVal szEquipID As String, ByVal szProcCD As String) As Integer
        Dim szSql As String = ""
        szSql &= "SELECT Inspection_Qty_Type  "
        szSql &= "  FROM TRA.L_LOT_DEFECT "
        szSql &= "WHERE LOT_NO = ? "
        szSql &= "AND EQUIP_ID = ? "
        szSql &= "AND KOUTEI_CD = ? "

        Dim dt As DataTable = DBacs.ExecuteSql(szSql, -1, szLotNo, szEquipID, szProcCD)
        If IsNothing(dt) Then
            GetInspection_Qty_Type = 0
        Else
            If dt.Rows.Count = 0 Then
                GetInspection_Qty_Type = 0
            Else
                GetInspection_Qty_Type = CInt(dt.Rows(0)("Inspection_Qty_Type").ToString)
            End If
        End If
    End Function

    Public Function GetMoldDefectData(ByVal szLotNo As String) As DataTable
        Dim szSql As String = ""
        szSql &= "SELECT def.KOUTEI_CD, cd.proc_name, "
        szSql &= "  def.CAT_CD, def.CAT_NAME, def.CAT_CNT "
        szSql &= "FROM ( "
        szSql &= "  SELECT KOUTEI_CD, CAT_CD, CAT_NAME, SUM(CAT_CNT) as CAT_CNT "
        szSql &= "  FROM TRA.L_LOT_DEFECT "
        szSql &= "  WHERE LOT_NO = '" & szLotNo & "' "
        szSql &= "  AND KOUTEI_CD IN ('4303','4306') "
        szSql &= "  GROUP BY KOUTEI_CD, CAT_CD, CAT_NAME "
        szSql &= ") def "
        szSql &= "INNER JOIN per.t_proc_cd cd "
        szSql &= "ON def.KOUTEI_CD = cd.proc_cd "
        szSql &= "ORDER BY KOUTEI_CD "

        Dim dt As DataTable = DBacs.ExecuteSql(szSql, -1, szLotNo)
        If IsNothing(dt) Then
            GetMoldDefectData = New DataTable
        Else
            GetMoldDefectData = dt
        End If
    End Function

    '>>20171130 兪
    Public Function GetDefectHis(ByVal szLotNo As String, ByVal szProcCD As String) As DataTable
        Dim szSql As String = ""
        szSql &= "SELECT def.KOUTEI_CD, cd.proc_name, "
        szSql &= "  CASE WHEN def.complete_qty IS NULL THEN '0' ELSE def.complete_qty END as complete_qty, "
        szSql &= "  CASE WHEN def.CAT_CNT IS NULL THEN 0 ELSE def.CAT_CNT END as CAT_CNT, "
        szSql &= "  CASE WHEN def.cross_qty IS NULL THEN '0' ELSE def.cross_qty END as cross_qty, "
        szSql &= "  CASE WHEN def.cross_frmqty IS NULL THEN '0' ELSE def.cross_frmqty END as cross_frmqty, "
        szSql &= "  CASE WHEN def.frame_qty IS NULL THEN '0' ELSE def.frame_qty END as frame_qty "
        szSql &= "FROM ( "
        szSql &= "  SELECT h.begin_time, h.koutei_cd as KOUTEI_CD, MAX(complete_qty) as complete_qty, SUM(d.CAT_CNT) as CAT_CNT, "
        szSql &= "    MAX(cross_qty) as cross_qty, MAX(cross_frmqty) as cross_frmqty, MAX(frame_qty) as frame_qty "
        szSql &= "  FROM mat.l_sf_work_rslt_head h "
        'szSql &= "  inner join per.l_jyokendata j "
        'szSql &= "  ON h.lot_no = '" & szLotNo & "' "
        'szSql &= "  AND h.koutei_cd = j.komokudata "
        szSql &= "  left outer join TRA.L_LOT_DEFECT d "
        szSql &= "  ON d.LOT_NO = '" & szLotNo & "' "
        szSql &= "  AND d.dte >= h.begin_time "
        szSql &= "  AND d.dte <= h.complete_time "
        szSql &= "  AND d.koutei_cd = h.koutei_cd "
        szSql &= "  WHERE h.lot_no = '" & szLotNo & "' "
        szSql &= "  and h.type = '3' "
        'szSql &= "  AND J.kotei = 'PROCESS_INFO' "
        'szSql &= "  AND to_number(j.komokuname, '99') <= ( "
        'szSql &= "    SELECT to_number(komokuname, '99') "
        'szSql &= "    FROM per.l_jyokendata "
        'szSql &= "    WHERE klotno = '" & szLotNo & "' "
        'szSql &= "    AND kotei = 'PROCESS_INFO' "
        'szSql &= "    AND komokudata = '" & szProcCD & "' "
        'szSql &= "    LIMIT 1 "
        'szSql &= "  ) "
        szSql &= "  GROUP BY h.koutei_cd, h.begin_time "
        szSql &= ") def "
        szSql &= "INNER JOIN per.t_proc_cd cd "
        szSql &= "ON cd.proc_cd=def.KOUTEI_CD "
        szSql &= "order by begin_time "

        Dim dt As DataTable = DBacs.ExecuteSql(szSql, -1, szLotNo)
        If IsNothing(dt) Then
            GetDefectHis = New DataTable
        Else
            GetDefectHis = dt
        End If
    End Function
    '<<20171130 兪

    '>> 2018/02/19 安部 検査数量タイプ 追加
    'Public Function SaveDefectData(ByVal szLotNo As String, ByVal szEquipID As String, ByVal szEquipNo As String, ByVal dtDefect As DataTable, ByVal szkouteiCD As String)
    Public Function SaveDefectData(ByVal szLotNo As String, ByVal szEquipID As String, ByVal szEquipNo As String, ByVal dtDefect As DataTable, ByVal szkouteiCD As String, Optional ByVal iInspection_Qty_Type As Integer = 0)
        '<< 2018/02/19 安部 検査数量タイプ 追加
        Dim szSqlDel As String = ""
        '>> 2018/02/19 安部 検査数量タイプ 追加
        'szSqlDel &= "DELETE FROM TRA.L_LOT_DEFECT "
        'szSqlDel &= "  WHERE LOT_NO = '" & szLotNo & "' "
        'szSqlDel &= "AND EQUIP_ID = '" & szEquipID & "' "
        szSqlDel &= "DELETE FROM TRA.L_LOT_DEFECT "
        szSqlDel &= "  WHERE LOT_NO = ? "
        szSqlDel &= "AND EQUIP_ID = ? "

        'DBacs.ExecuteUpdateSql(szSqlDel, -1, szLotNo)
        DBacs.ExecuteUpdateSql(szSqlDel, -1, szLotNo, szEquipID)
        '<< 2018/02/19 安部 検査数量タイプ 追加

        Dim szYMD As String = DateTime.Now.ToString("yyyyMMdd")
        Dim szSql As String = ""
        szSql &= "INSERT INTO TRA.L_LOT_DEFECT( "
        '>> 2018/02/19 安部 検査数量タイプ 追加
        'szSql &= "  LOT_NO, EQUIP_ID, EQUIP_NO, CAT_CD, CAT_NAME, CAT_CNT, KOUTEI_CD, YYYYMMDD "
        szSql &= "  LOT_NO, EQUIP_ID, EQUIP_NO, CAT_CD, CAT_NAME, CAT_CNT, KOUTEI_CD, YYYYMMDD, Inspection_Qty_Type  "
        szSql &= ") "
        'szSql &= "VALUES ( ?, ?, ?, ?, ?, ?, ?, ?) "
        szSql &= "VALUES ( ?, ?, ?, ?, ?, ?, ?, ?, ?) "
        '<< 2018/02/19 安部 検査数量タイプ 追加

        Dim iCnt As Integer = 0
        For Each dr As DataRow In dtDefect.Rows
            '>> 2018/02/19 安部 検査数量タイプ 追加
            'iCnt += DBacs.ExecuteUpdateSql(szSql, -1,
            '                       szLotNo, szEquipID, szEquipNo, dr("コード"), dr("不良内容"), dr("数量"), szkouteiCD, szYMD)
            iCnt += DBacs.ExecuteUpdateSql(szSql, -1,
                                   szLotNo, szEquipID, szEquipNo, dr("コード"), dr("不良内容"), dr("数量"), szkouteiCD, szYMD, iInspection_Qty_Type)
            '<< 2018/02/19 安部 検査数量タイプ 追加
        Next

        SaveDefectData = iCnt
    End Function

    Public Function GetDefectCategory(ByVal iEquipType As Integer) As DataTable
        Dim szSql As String = ""
        szSql &= "SELECT DATA_VALUE as コード, to_char(CATEGORY_NAME) as 不良内容 " 'UPD MIAO 2018/10/22
        szSql &= "  FROM TRA.T_NG_CATEGORY_INFO "
        '>>20171010 兪
        szSql &= "WHERE DATA_TYPE = 4 "
        '<<20171010 兪
        '>>20180115 安部
        'szSql &= "AND EQUIP_TYPE = " & iEquipType
        szSql &= "AND EQUIP_TYPE = ? "
        szSql &= "ORDER BY to_char(CATEGORY_NAME) " 'UPD 苗　2018/1/23 'UPD MIAO 2018/10/22
        '<<20180115 安部

        Dim dt As DataTable = DBacs.ExecuteSql(szSql, -1, iEquipType)
        If IsNothing(dt) Then
            GetDefectCategory = New DataTable
        Else
            For Each row As DataRow In dt.Rows
                Dim szCodeInfo As String() = row("不良内容").ToString.Split(":")
                If szCodeInfo.Length > 1 Then
                    row("コード") = szCodeInfo(0)
                    row("不良内容") = szCodeInfo(1)
                End If
            Next
            GetDefectCategory = dt
        End If
    End Function

    Public Function CheckWarning(ByVal szProcsCD As String, ByVal szKindNM As String, ByVal szCustomer As String,
                                 ByVal szPkgNm As String, ByVal szCata() As String) As DataTable
        Dim szSql As String = ""
        szSql &= "  SELECT IS_EXCLUDED, CATA_CD, OUTPUT_TYPE, "
        szSql &= "    replace(THRESHOLD1, '%', '') AS THRESHOLD1, OVER_UNDER1, "
        szSql &= "    replace(THRESHOLD2, '%', '') AS THRESHOLD2, OVER_UNDER2 "
        szSql &= "  FROM KAD.T_NG_CATEGORY_WARNING "
        szSql &= "  WHERE KOUTEI_CD = '" & szProcsCD & "' "
        szSql &= "  AND CUSTOMER_NM IN('" & szCustomer & "', '-') "
        szSql &= "  AND replace(PKG_NM, ' ', '') IN ('" & szPkgNm & "', '-') "
        szSql &= "  AND replace(KIND_NM, ' ', '') IN ('" & szKindNM & "', '-') "
        szSql &= "  AND IS_EXCLUDED = '0' "
        szSql &= "  AND CATA_CD <> '-' "
        szSql &= "  AND OUTPUT_TYPE <> '-' "
        szSql &= "  AND ((THRESHOLD1 <> '-' "
        szSql &= "  AND OVER_UNDER1 <> '-') "
        szSql &= "  OR (THRESHOLD2 <> '-' "
        szSql &= "  AND OVER_UNDER2 <> '-')) "
        szSql &= "  AND CATA_CD IN ("

        If szCata.Length > 0 Then
            szSql &= String.Format("'{0}'", szCata(0))
            Dim i As Integer
            For i = 1 To szCata.Length - 1
                szSql &= String.Format(", '{0}'", szCata(i))
            Next
        End If
        szSql &= ") "

        szSql &= "UNION ALL "

        szSql &= "  SELECT IS_EXCLUDED, CATA_CD, OUTPUT_TYPE, "
        szSql &= "    replace(THRESHOLD1, '%', '') AS THRESHOLD1, OVER_UNDER1, "
        szSql &= "    replace(THRESHOLD2, '%', '') AS THRESHOLD2, OVER_UNDER2 "
        szSql &= "  FROM KAD.T_NG_CATEGORY_WARNING WAN"
        szSql &= "  WHERE KOUTEI_CD = '" & szProcsCD & "' "
        szSql &= "  AND CUSTOMER_NM IN ('" & szCustomer & "', '-') "
        szSql &= "  AND PKG_NM IN ('" & szPkgNm & "', '-') "
        szSql &= "  AND KIND_NM IN ('" & szKindNM & "', '-') "
        szSql &= "  AND IS_EXCLUDED = '1' "
        szSql &= "  AND CATA_CD <> '-' "
        szSql &= "  AND OUTPUT_TYPE <> '-' "
        szSql &= "  AND ((THRESHOLD1 <> '-' "
        szSql &= "  AND OVER_UNDER1 <> '-') "
        szSql &= "  OR (THRESHOLD2 <> '-' "
        szSql &= "  AND OVER_UNDER2 <> '-')) "

        szSql &= "UNION ALL "

        szSql &= "  SELECT '0', CATA_CD, OUTPUT_TYPE, "
        szSql &= "    replace(THRESHOLD1, '%', '') AS THRESHOLD1, OVER_UNDER1, "
        szSql &= "    replace(THRESHOLD2, '%', '') AS THRESHOLD2, OVER_UNDER2 "
        szSql &= "  FROM KAD.T_NG_CATEGORY_WARNING WAN"
        szSql &= "  WHERE KOUTEI_CD = '" & szProcsCD & "' "
        szSql &= "  AND CUSTOMER_NM IN ('" & szCustomer & "', '-') "
        szSql &= "  AND PKG_NM IN ('" & szPkgNm & "', '-') "
        szSql &= "  AND KIND_NM IN ('" & szKindNM & "', '-') "
        'szSql &= "  AND IS_EXCLUDED = '1' "
        szSql &= "  AND CATA_CD <> '-' "
        szSql &= "  AND OUTPUT_TYPE <> '-' "
        szSql &= "  AND ((THRESHOLD1 <> '-' "
        szSql &= "  AND OVER_UNDER1 <> '-') "
        szSql &= "  OR (THRESHOLD2 <> '-' "
        szSql &= "  AND OVER_UNDER2 <> '-')) "
        szSql &= "  AND CATA_CD='ALL' "

        Dim dt As DataTable = DBacs.ExecuteSql(szSql, -1)
        If IsNothing(dt) Then
            CheckWarning = New DataTable
        Else
            CheckWarning = dt
        End If
    End Function

    Public Function GetRackFrameInfo(ByVal szLotNo As String) As DataTable
        Dim szSql As String = ""
        szSql &= "SELECT LOT_NO, RACK_NO, RACK_ID, RACK_TANSF, "
        szSql &= "  FRAME_CNT, FRM_COL_CNT, FRM_ROW_CNT, FRM_TANSF, PQC_SUBMIT "
        szSql &= "FROM TRA.L_FRAME_ADDR "
        szSql &= "WHERE LOT_NO = '" & szLotNo & "'"

        Dim dt As DataTable = DBacs.ExecuteSql(szSql, -1)
        If IsNothing(dt) Then
            GetRackFrameInfo = New DataTable
        Else
            GetRackFrameInfo = dt
        End If
    End Function

    Public Function SaveRackFrameInfo(ByVal dtFrameInfo As DataTable, ByVal szLotNo As String) As Integer
        Dim szSql As String = ""
        Dim iCount As Integer = 0

        '削除
        szSql = ""
        Dim DelRow As DataTable = dtFrameInfo.GetChanges(DataRowState.Deleted)
        If Not (DelRow Is Nothing) Then
            szSql &= "DELETE FROM TRA.L_FRAME_ADDR "
            szSql &= "WHERE LOT_NO = ? "
            szSql &= "AND RACK_NO = ? "

            For Each dr As DataRow In DelRow.Rows
                Dim cnt As Integer
                With dr
                    cnt = DBacs.ExecuteUpdateSql(szSql, -1,
                        szLotNo, .Item("RACK_NO", DataRowVersion.Original))
                End With

                If cnt < 0 Then
                    DBacs.RollbackTransaction()
                    Return cnt
                Else
                    iCount += cnt
                End If
            Next
        End If

        '新規
        DBacs.BeginTransaction()
        Dim NewRows As DataTable = dtFrameInfo.GetChanges(DataRowState.Added)
        If Not (NewRows Is Nothing) Then
            szSql &= "INSERT INTO TRA.L_FRAME_ADDR(  "
            szSql &= "  LOT_NO, RACK_NO, RACK_ID, RACK_TANSF, "
            szSql &= "  FRAME_CNT, FRM_COL_CNT, FRM_ROW_CNT, FRM_TANSF, PQC_SUBMIT "
            szSql &= ") "
            szSql &= "VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?) "

            For Each dr As DataRow In NewRows.Rows
                Dim cnt As Integer
                With dr
                    cnt = DBacs.ExecuteUpdateSql(szSql, -1,
                        .Item("LOT_NO"), .Item("RACK_NO"), .Item("RACK_ID"), .Item("RACK_TANSF"),
                        .Item("FRAME_CNT"), .Item("FRM_COL_CNT"), .Item("FRM_ROW_CNT"), .Item("FRM_TANSF"), .Item("PQC_SUBMIT"))
                End With

                If cnt < 0 Then
                    DBacs.RollbackTransaction()
                    Return cnt
                Else
                    iCount += cnt
                End If
            Next
        End If

        '変更
        szSql = ""
        Dim ModRow As DataTable = dtFrameInfo.GetChanges(DataRowState.Modified)
        If Not (ModRow Is Nothing) Then
            szSql &= "UPDATE TRA.L_FRAME_ADDR SET  "
            szSql &= "  RACK_ID = ?, "
            szSql &= "  RACK_TANSF = ?, "
            szSql &= "  FRAME_CNT = ?, "
            szSql &= "  FRM_COL_CNT = ?, "
            szSql &= "  FRM_ROW_CNT = ?, "
            szSql &= "  FRM_TANSF = ?, "
            szSql &= "  PQC_SUBMIT = ? "
            szSql &= "WHERE LOT_NO = ? "
            szSql &= "AND RACK_NO = ? "

            For Each dr As DataRow In ModRow.Rows
                Dim cnt As Integer
                With dr
                    cnt = DBacs.ExecuteUpdateSql(szSql, -1,
                        .Item("RACK_ID"), .Item("RACK_TANSF"), .Item("FRAME_CNT"),
                        .Item("FRM_COL_CNT"), .Item("FRM_ROW_CNT"), .Item("FRM_TANSF"), .Item("PQC_SUBMIT"),
                        szLotNo, .Item("RACK_NO"))
                End With

                If cnt < 0 Then
                    DBacs.RollbackTransaction()
                    Return cnt
                Else
                    iCount += cnt
                End If
            Next
        End If

        DBacs.CommitTransaction()
        SaveRackFrameInfo = iCount
    End Function

    Public Function GetDefectMapInfo(ByVal szLotNo As String, ByVal szEquipID As String) As DataTable
        Dim szSql As String = ""
        szSql &= "SELECT ADR.LOT_NO, ADR.RACK_NO, ADR.RACK_ID, "
        szSql &= "  DEF.DANSU, DEF.MAP_X, DEF.MAP_Y, DEF.CAT_CD, DEF.CAT_NAME, DEF.PQC_SUBMIT "
        szSql &= "FROM TRA.L_LOT_DEFECT DEF "
        szSql &= "INNER JOIN TRA.L_FRAME_ADDR ADR "
        szSql &= "ON DEF.LOT_NO=ADR.LOT_NO "
        szSql &= "AND DEF.RACK_NO=ADR.RACK_NO "
        szSql &= "WHERE ADR.LOT_NO = '" & szLotNo & "' "
        szSql &= "AND TO_NUMBER(DEF.MAP_X,'999') <= ADR.FRM_COL_CNT "
        szSql &= "AND TO_NUMBER(DEF.MAP_Y,'999') <= ADR.FRM_ROW_CNT "
        'szSql &= "AND DEF.EQUIP_ID = '" & szEquipID & "' "
        szSql &= "ORDER BY ADR.RACK_NO, DEF.DANSU "

        Dim dt As DataTable = DBacs.ExecuteSql(szSql, -1)
        If IsNothing(dt) Then
            GetDefectMapInfo = New DataTable
        Else
            GetDefectMapInfo = dt
        End If
    End Function

    Public Function SaveDefectMapInfo(ByVal dtMapInfo As DataTable, ByVal szLotNo As String, ByVal szEquipID As String, ByVal szEquipNo As String) As Integer
        Dim szSql As String = ""
        Dim iCount As Integer = 0

        DBacs.BeginTransaction()
        Dim DelRow As DataTable = dtMapInfo.GetChanges(DataRowState.Deleted)
        If Not (DelRow Is Nothing) Then
            szSql &= "DELETE FROM TRA.L_LOT_DEFECT "
            szSql &= "WHERE LOT_NO = ? "
            szSql &= "AND RACK_NO = ? "
            'szSql &= "AND EQUIP_ID = ? "
            szSql &= "AND DANSU = ? "
            szSql &= "AND MAP_X = ? "
            szSql &= "AND MAP_Y = ? "

            For Each dr As DataRow In DelRow.Rows
                Dim cnt As Integer
                With dr
                    cnt = DBacs.ExecuteUpdateSql(szSql, -1,
                        szLotNo, .Item("RACK_NO", DataRowVersion.Original),
                        .Item("DANSU", DataRowVersion.Original), .Item("MAP_X", DataRowVersion.Original),
                        .Item("MAP_Y", DataRowVersion.Original))
                End With

                If cnt < 0 Then
                    DBacs.RollbackTransaction()
                    Return cnt
                Else
                    iCount += cnt
                End If
            Next

        End If

        szSql = ""
        Dim NewRows As DataTable = dtMapInfo.GetChanges(DataRowState.Added)
        If Not (NewRows Is Nothing) Then
            szSql &= "INSERT INTO TRA.L_LOT_DEFECT(  "
            szSql &= "  LOT_NO, RACK_ID, RACK_NO, EQUIP_ID, EQUIP_NO, DANSU, "
            szSql &= "  MAP_X, MAP_Y, CAT_CD, CAT_NAME, CAT_CNT, PQC_SUBMIT "
            szSql &= ") "
            szSql &= "VALUES (?, ?, ?, ?, ?, ?, " &
                             "?, ?, ?, ?, ?, ?) "

            For Each dr As DataRow In NewRows.Rows
                Dim cnt As Integer
                With dr
                    cnt = DBacs.ExecuteUpdateSql(szSql, -1,
                        .Item("LOT_NO"), .Item("RACK_ID"), .Item("RACK_No"), szEquipID, szEquipNo, .Item("DANSU"),
                        .Item("MAP_X"), .Item("MAP_Y"), .Item("CAT_CD"), .Item("CAT_NAME"), 1, .Item("PQC_SUBMIT"))
                End With

                If cnt < 0 Then
                    DBacs.RollbackTransaction()
                    Return cnt
                Else
                    iCount += cnt
                End If
            Next
        End If

        szSql = ""
        Dim ModRow As DataTable = dtMapInfo.GetChanges(DataRowState.Modified)
        If Not (ModRow Is Nothing) Then
            szSql &= "UPDATE TRA.L_LOT_DEFECT SET  "
            szSql &= "  RACK_ID = ?, "
            szSql &= "  CAT_CD = ?, "
            szSql &= "  CAT_NAME = ?, "
            szSql &= "  CAT_CNT = ?, "
            szSql &= "  PQC_SUBMIT = ? "
            szSql &= "WHERE LOT_NO = ? "
            szSql &= "AND RACK_NO = ? "
            'szSql &= "AND EQUIP_ID = ? "
            szSql &= "AND DANSU = ? "
            szSql &= "AND MAP_X = ? "
            szSql &= "AND MAP_Y = ? "

            For Each dr As DataRow In ModRow.Rows
                Dim cnt As Integer
                With dr
                    cnt = DBacs.ExecuteUpdateSql(szSql, -1,
                        .Item("RACK_ID"), .Item("CAT_CD"), .Item("CAT_NAME"), 1, .Item("PQC_SUBMIT"),
                        szLotNo, .Item("RACK_NO"), .Item("DANSU"), .Item("MAP_X"), .Item("MAP_Y"))
                End With

                If cnt < 0 Then
                    DBacs.RollbackTransaction()
                    Return cnt
                Else
                    iCount += cnt
                End If
            Next
        End If

        DBacs.CommitTransaction()
        SaveDefectMapInfo = iCount
    End Function

    '>>20180117 佐川
    '不良数履歴テーブル書き込み
    Public Function InsertHistryDefect(ByVal lot_no As String, ByVal procCd As String, ByVal equipID As String, ByVal diffLotFlg As Boolean) As Integer
        Dim szSql As String = ""

        '不良数履歴テーブル書き込み
        szSql &= " INSERT INTO TRA.L_LOT_DEFECT_HISTORY "
        szSql &= " ( "
        szSql &= "   SYS_CMD_ID, EQUIP_ID, EQUIP_NO, lot_no, PROC_TIMES, CAT_CD, "
        szSql &= "   CAT_NAME, CAT_CNT, KOUTEI_CD, RACK_ID, DANSU, MAP_X, MAP_Y, "
        szSql &= "   DTE, YYYYMMDD, RACK_NO, PQC_SUBMIT, IS_EXCLUDED, CUNSTOM_NM, "
        '>> 2018/02/19 安部 検査数量タイプ 追加
        'szSql &= "   PKG_NM, KIND_NM, THRESHOLD, OVER_UNDER, OUTPUT_TYPE "
        szSql &= "   PKG_NM, KIND_NM, THRESHOLD, OVER_UNDER, OUTPUT_TYPE, Inspection_Qty_Type "
        '<< 2018/02/19 安部 検査数量タイプ 追加
        szSql &= "  ) "
        szSql &= " SELECT "
        szSql &= "   A.SYS_CMD_ID, A.EQUIP_ID, A.EQUIP_NO, A.LOT_NO, B.PROC_TIMES, A.CAT_CD, "
        szSql &= "   A.CAT_NAME, A.CAT_CNT, A.KOUTEI_CD, A.RACK_ID, A.DANSU, A.MAP_X, A.MAP_Y, "
        szSql &= "   A.DTE, A.YYYYMMDD, A.RACK_NO, A.PQC_SUBMIT, A.IS_EXCLUDED, A.CUNSTOM_NM, "
        '>> 2018/02/19 安部 検査数量タイプ 追加
        'szSql &= "   A.PKG_NM, A.KIND_NM, A.THRESHOLD, A.OVER_UNDER, A.OUTPUT_TYPE "
        szSql &= "   A.PKG_NM, A.KIND_NM, A.THRESHOLD, A.OVER_UNDER, A.OUTPUT_TYPE, Inspection_Qty_Type "
        '<< 2018/02/19 安部 検査数量タイプ 追加
        szSql &= " FROM TRA.L_LOT_DEFECT A "
        szSql &= " JOIN ("
        If diffLotFlg Then
            szSql &= "  SELECT DIFF_LOT As LOT , EQUIP_ID, PROC_TIMES "
        Else
            szSql &= "  SELECT LOT_NO As LOT , EQUIP_ID, PROC_TIMES"
        End If
        szSql &= "      FROM MAT.L_SF_WORK_RSLT_HEAD "
        If diffLotFlg Then
            szSql &= "  WHERE DIFF_LOT = ? "
        Else
            szSql &= "  WHERE LOT_NO = ? "
        End If
        szSql &= "      AND EQUIP_ID = ? "
        szSql &= "      AND KOUTEI_CD = ? "
        szSql &= "      ORDER BY PROC_TIMES DESC "
        szSql &= "      LIMIT  1 "
        szSql &= "     ) B "
        szSql &= " ON A.EQUIP_ID = B.EQUIP_ID "
        szSql &= " AND A.LOT_NO = B.LOT "
        szSql &= " WHERE A.LOT_NO = ? "
        szSql &= " AND A.EQUIP_ID = ? "
        szSql &= " AND A.KOUTEI_CD = ? "

        '登録SQL実行 koutei_cd
        DBacs.ExecuteUpdateSql(szSql, -1, lot_no, equipID, procCd, lot_no, equipID, procCd)

        '不良数データ削除
        Dim szSqlDel As String = ""
        szSqlDel &= " DELETE "
        szSqlDel &= " FROM TRA.L_LOT_DEFECT "
        szSqlDel &= " WHERE LOT_NO = ? "
        szSqlDel &= " AND EQUIP_ID = ? "
        szSqlDel &= " AND KOUTEI_CD = ? "

        DBacs.ExecuteUpdateSql(szSqlDel, -1, lot_no, equipID, procCd)

        Return 0
    End Function
    '<<20180117 佐川

    '>>20180118 佐川
    '不良カテゴリ削除処理
    Public Function DeleteDefectCategory(ByVal lot_no As String, ByVal procCd As String, ByVal equipID As String) As Integer

        '不良数データ削除
        Dim szSqlDel As String = ""
        szSqlDel &= " DELETE "
        szSqlDel &= " FROM TRA.L_LOT_DEFECT "
        szSqlDel &= " WHERE LOT_NO = ? "
        szSqlDel &= " AND EQUIP_ID = ? "
        szSqlDel &= " AND KOUTEI_CD = ? "

        DBacs.ExecuteUpdateSql(szSqlDel, -1, lot_no, equipID, procCd)

        Return 0
    End Function
    '<<20180118 佐川
    'ADD　MIAO　2018/8/23 BEGIN
    '工程別の不良数合計の取得
    Public Function getDefectDataSUM(ByVal lot_no As String, ByVal procCd As String) As Integer
        Dim szSql As String = ""
        szSql &= " SELECT SUM(CAT_CNT) FROM TRA.L_LOT_DEFECT "
        szSql &= " WHERE  LOT_NO =? AND KOUTEI_CD=? " 'UPD Miao 2018/8/30

        Dim dt As DataTable = DBacs.ExecuteSql(szSql, -1, lot_no, procCd)
        If IsDBNull(dt.Rows(0)(0)) Then 'UPD Miao 2018/8/30
            getDefectDataSUM = 0
        Else
            getDefectDataSUM = dt.Rows(0)(0)
        End If
    End Function
    'ADD　MIAO　2018/8/23 END
    'ADD MIAO 2019/10/1 begin
    ''' <summary>
    ''' ラックNoからロットNoを検索して返却します。
    ''' </summary>
    ''' <param name="rackNo"></param>
    ''' <returns></returns>
    Public Function GetLotNoByRack(ByVal rackNo As String) As String
        Dim szSql As String = String.Empty
        szSql &= "SELECT LOT_NO "
        szSql &= "FROM TRA.L_RACK_INFO "
        szSql &= "WHERE RACK_ID = '" & rackNo & "' "

        Dim lotNo As String = IIf(IsDBNull(DBacs.ExccuteScalarSql(szSql, -1)) = True, "", DBacs.ExccuteScalarSql(szSql, -1))
        If IsNothing(lotNo) Then lotNo = ""
        Return lotNo

    End Function
    ''' <summary>
    ''' ラックNoからロットNoを検索して返却します。
    ''' </summary>
    ''' <param name="rackNo"></param>
    ''' <returns>Lot No. List</returns>
    Public Function GetLotNoListByRack(ByVal rackNo As String, kouteiCD As String()) As DataTable
        Dim szSql As String = String.Empty
        Dim dtLotList As DataTable
        Dim strProcCD As String = ""

        szSql &= "SELECT LOT_NO "
        szSql &= "FROM TRA.L_RACK_INFO "
        szSql &= "WHERE RACK_ID = '" & rackNo & "' "
        szSql &= "Order by LOT_NO ASC"

        Dim dt As DataTable = DBacs.ExecuteSql(szSql, -1)
        If IsNothing(dt) OrElse dt.Rows.Count = 0 Then
            Return New DataTable
        Else

            For i As Integer = 0 To kouteiCD.Length - 1
                strProcCD &= "'" & kouteiCD(i) & "',"
            Next
            strProcCD = Left(strProcCD, Len(strProcCD) - 1)

            szSql = ""
            szSql &= "SELECT LOT_NO "
            szSql &= " FROM PER.L_TAG_LOTDATA"
            szSql &= " WHERE WAFER_LOTNO='" & dt(0)(0) & "' "
            szSql &= " AND KOUTEI_CODE IN (" & strProcCD & ")" 'DEL MIAOJF 2020040
            szSql &= " Order by LOT_NO ASC"

            dtLotList = DBacs.ExecuteSql(szSql, -1)
            If IsNothing(dtLotList) Then
                Return New DataTable
            Else
                Return dtLotList
            End If
        End If
    End Function

    Public Sub insertRackInfo(rackNo As String)
        Dim szSql As String = String.Empty
        Dim dtLotList As DataTable

        szSql = ""
        szSql &= "SELECT WAFER_LOTNO "
        szSql &= " FROM PER.L_TAG_LOTDATA"
        szSql &= " WHERE CONTAINER1='" & rackNo & "'"
        szSql &= " AND KOUTEI_CODE IN ('A26','A10','A121','A122','A12J','A41','A42','A51','A53','A54','S121','S122','S41','S42')"
        szSql &= " Order by TOUROKUDATE DESC"
        dtLotList = DBacs.ExecuteSql(szSql, -1)
        If dtLotList.Rows.Count > 0 Then
            Dim m_cData As New clsDbCondData
            szSql = ""
            szSql = "delete TRA.L_RACK_INFO where rack_id=?"
            DBacs.ExecuteUpdateSql(szSql, -1, rackNo)
            PutLog(Me.ToString, "rackinfo", "delete rackid 1" & rackNo)
            szSql = ""
            szSql = "insert into TRA.L_RACK_INFO(RACK_ID,LOT_NO,PLACE,SET_TYPE,SET_DATE,SEQ,WAFER_LOT) values (?,?,null,'SF',?,null,?)"
            DBacs.ExecuteUpdateSql(szSql, -1, rackNo, dtLotList(0)(0), m_cData.GetServerTime(), dtLotList(0)(0))
            PutLog(Me.ToString, "rackinfo", "insert waferNo 1" & rackNo & dtLotList(0)(0))

        End If
    End Sub

    Public Sub insertLotRackInfo(rackNo As String)
        Dim szSql As String = String.Empty
        Dim dtLotList As DataTable

        szSql = ""
        szSql &= "SELECT LOT_NO "
        szSql &= " FROM PER.L_TAG_LOTDATA"
        szSql &= " WHERE CONTAINER1='" & rackNo & "'"
        szSql &= " AND KOUTEI_CODE IN ('A26','A54')"
        szSql &= " Order by TOUROKUDATE DESC"
        dtLotList = DBacs.ExecuteSql(szSql, -1)
        If dtLotList.Rows.Count > 0 Then
            Dim m_cData As New clsDbCondData
            szSql = ""
            szSql = "delete TRA.L_RACK_INFO where rack_id=?"
            DBacs.ExecuteUpdateSql(szSql, -1, rackNo)
            PutLog(Me.ToString, "rackinfo", "delete rackid 12" & rackNo)
            szSql = ""
            szSql = "insert into TRA.L_RACK_INFO(RACK_ID,LOT_NO,PLACE,SET_TYPE,SET_DATE,SEQ,WAFER_LOT) values (?,?,null,'SF',?,null,null)"
            DBacs.ExecuteUpdateSql(szSql, -1, rackNo, dtLotList(0)(0), m_cData.GetServerTime())
            PutLog(Me.ToString, "rackinfo", "insert LotNo 12" & rackNo & dtLotList(0)(0))

        End If
    End Sub
    Public Sub setNewRack(lotno As String, oldRack As String, newRack As String)
        Dim szSql As String = ""
        szSql = "update TRA.L_RACK_INFO set RACK_ID= ? where RACK_ID=? and LOT_NO=? "
        DBacs.ExecuteUpdateSql(szSql, -1, newRack, oldRack, lotno)
        PutLog(Me.ToString, "rackinfo", "setNewRack:" & oldRack & newRack & lotno)
    End Sub
    Public Sub delRack(lotno As String, rackno As String)
        Dim szSql As String = ""
        szSql = "delete TRA.L_RACK_INFO where RACK_ID=? and LOT_NO=? "
        DBacs.ExecuteUpdateSql(szSql, -1, rackno, lotno)
        PutLog(Me.ToString, "rackinfo", "delRack:" & rackno & lotno)
    End Sub

    'Public Function SelectLotRackInfo(rackNo As String) As String
    '    Dim szSql As String = String.Empty
    '    Dim dtLotList As DataTable
    '    Dim rt As String = ""
    '    szSql = ""
    '    szSql &= "SELECT LOT_NO "
    '    szSql &= " FROM PER.L_TAG_LOTDATA"
    '    szSql &= " WHERE CONTAINER1='" & rackNo & "'"
    '    szSql &= " AND KOUTEI_CODE IN ('A26','A54')"

    '    szSql &= " Order by TOUROKUDATE DESC"
    '    dtLotList = DBacs.ExecuteSql(szSql, -1)
    '    If dtLotList.Rows.Count > 0 Then
    '        rt = dtLotList(0)(0)
    '    End If
    '    Return rt
    'End Function
    Public Sub insertRackInfo(lotno As String, procCD As String, rackNo1 As String, rackNo2 As String, rackNo3 As String, rackNo4 As String, rackNo5 As String)
        Dim szSql As String
        Dim m_cData As New clsDbCondData
        szSql = "delete TRA.L_RACK_INFO where LOT_NO=?"
        DBacs.ExecuteUpdateSql(szSql, -1, lotno)
        PutLog(Me.ToString, "rackinfo", "delete lotno 2" & lotno)

        If procCD = "S41" Then
            szSql = "delete TRA.L_RACK_INFO where RACK_ID=?"
            DBacs.ExecuteUpdateSql(szSql, -1, rackNo1)
            PutLog(Me.ToString, "rackinfo", "delete rackid 3" & rackNo1)

            szSql = ""
            szSql = "insert into TRA.L_RACK_INFO(RACK_ID,LOT_NO,PLACE,SET_TYPE,SET_DATE,SEQ,WAFER_LOT) values (?,?,null,'SF',?,null,?)"
            DBacs.ExecuteUpdateSql(szSql, -1, rackNo1, lotno, m_cData.GetServerTime(), lotno)
            PutLog(Me.ToString, "rackinfo", "insert waferNo 3" & rackNo1 & lotno)

        Else
            If rackNo1 <> "" Then
                szSql = "delete TRA.L_RACK_INFO where RACK_ID=?"
                DBacs.ExecuteUpdateSql(szSql, -1, rackNo1)
                PutLog(Me.ToString, "rackinfo", "delete rackid 4" & rackNo1)

                szSql = ""
                szSql = "insert into TRA.L_RACK_INFO(RACK_ID,LOT_NO,PLACE,SET_TYPE,SET_DATE,SEQ,WAFER_LOT) values (?,?,null,'SF',?,null,null)"
                DBacs.ExecuteUpdateSql(szSql, -1, rackNo1, lotno, m_cData.GetServerTime())
                PutLog(Me.ToString, "rackinfo", "insert lotno 4" & rackNo1 & lotno)

            End If
            If rackNo2 <> "" Then
                szSql = "delete TRA.L_RACK_INFO where RACK_ID=?"
                DBacs.ExecuteUpdateSql(szSql, -1, rackNo2)
                PutLog(Me.ToString, "rackinfo", "delete rackid 5" & rackNo2)

                szSql = ""
                szSql = "insert into TRA.L_RACK_INFO(RACK_ID,LOT_NO,PLACE,SET_TYPE,SET_DATE,SEQ,WAFER_LOT) values (?,?,null,'SF',?,null,null)"
                DBacs.ExecuteUpdateSql(szSql, -1, rackNo2, lotno, m_cData.GetServerTime())
                PutLog(Me.ToString, "rackinfo", "insert lotno 5" & rackNo2 & lotno)

            End If
            If rackNo3 <> "" Then
                szSql = "delete TRA.L_RACK_INFO where RACK_ID=?"
                DBacs.ExecuteUpdateSql(szSql, -1, rackNo3)
                PutLog(Me.ToString, "rackinfo", "delete rackid 6" & rackNo3)

                szSql = ""
                szSql = "insert into TRA.L_RACK_INFO(RACK_ID,LOT_NO,PLACE,SET_TYPE,SET_DATE,SEQ,WAFER_LOT) values (?,?,null,'SF',?,null,null)"
                DBacs.ExecuteUpdateSql(szSql, -1, rackNo3, lotno, m_cData.GetServerTime())
                PutLog(Me.ToString, "rackinfo", "insert lotno 6" & rackNo3 & lotno)

            End If
            If rackNo4 <> "" Then
                szSql = "delete TRA.L_RACK_INFO where RACK_ID=?"
                DBacs.ExecuteUpdateSql(szSql, -1, rackNo4)
                PutLog(Me.ToString, "rackinfo", "delete rackid 7" & rackNo4)

                szSql = ""
                szSql = "insert into TRA.L_RACK_INFO(RACK_ID,LOT_NO,PLACE,SET_TYPE,SET_DATE,SEQ,WAFER_LOT) values (?,?,null,'SF',?,null,null)"
                DBacs.ExecuteUpdateSql(szSql, -1, rackNo4, lotno, m_cData.GetServerTime())
                PutLog(Me.ToString, "rackinfo", "insert lotno 7" & rackNo4 & lotno)

            End If
            If rackNo5 <> "" Then
                szSql = "delete TRA.L_RACK_INFO where RACK_ID=?"
                DBacs.ExecuteUpdateSql(szSql, -1, rackNo5)
                PutLog(Me.ToString, "rackinfo", "delete rackid 8" & rackNo5)

                szSql = ""
                szSql = "insert into TRA.L_RACK_INFO(RACK_ID,LOT_NO,PLACE,SET_TYPE,SET_DATE,SEQ,WAFER_LOT) values (?,?,null,'SF',?,null,null)"
                DBacs.ExecuteUpdateSql(szSql, -1, rackNo5, lotno, m_cData.GetServerTime())
                PutLog(Me.ToString, "rackinfo", "insert lotno 8" & rackNo5 & lotno)

            End If

        End If

    End Sub

    Public Sub DelInsRackInfo(lotNo As String, rackNo As ArrayList)
        Dim szSql As String = String.Empty
        Dim key As String = String.Empty
        Dim m_cData As New clsDbCondData
        Try
            szSql = ""
            szSql &= "DELETE  TRA.L_RACK_INFO WHERE LOT_NO=?"
            key = lotNo
            DBacs.ExecuteUpdateSql(szSql, -1, lotNo)
            PutLog(Me.ToString, "rackinfo", "delete mold lotno 8" & lotNo)

            For i As Integer = 0 To rackNo.Count - 1
                szSql = ""
                szSql = "DELETE  TRA.L_RACK_INFO WHERE RACK_ID=?"
                key = rackNo(i)
                DBacs.ExecuteUpdateSql(szSql, -1, rackNo(i))
                PutLog(Me.ToString, "rackinfo", "delete mold RACK_ID 8-" & i & rackNo(i))

                szSql = ""
                szSql = "insert into TRA.L_RACK_INFO(RACK_ID,LOT_NO,PLACE,SET_TYPE,SET_DATE,SEQ,WAFER_LOT) values (?,?,null,'SF',?,null,?)"
                key = rackNo(i)
                DBacs.ExecuteUpdateSql(szSql, -1, rackNo(i), lotNo, m_cData.GetServerTime(), "")
                PutLog(Me.ToString, "rackinfo", "insert lotno 8-" & i & rackNo(i) & "lot:" & lotNo)

            Next

        Catch ex As Exception
            MsgBox(szSql & "key：" & key)
        End Try

    End Sub
    Public Function GetLastLotNoByGroupID(groupID As String, kouteiCD As String) As String
        Dim szSql As String = String.Empty
        Dim dtLotList As DataTable
        If groupID = "" Then Return ""

        szSql = ""
        szSql &= "SELECT LOT_NO "
        szSql &= " FROM PER.L_TAG_LOTDATA"
        szSql &= " WHERE WAFER_LOTNO='" & groupID & "' "
        szSql &= " AND KOUTEI_CODE='" & kouteiCD & "'" 'DEL MIAOJF 20200408 
        szSql &= " Order by LOT_NO ASC"
        dtLotList = DBacs.ExecuteSql(szSql, -1)
        If IsNothing(dtLotList) Then
            Return ""
        Else
            Return dtLotList.Rows(dtLotList.Rows.Count - 1)(0)
        End If
    End Function
    ''' <summary>
    ''' tra.l_frame_infoテーブルの情報を返却します。
    ''' </summary>
    ''' <param name="kouteiCode"></param>
    ''' <param name="lotNo"></param>
    ''' <returns>フレーム情報</returns>
    Public Function GetFrameInfo(ByVal kouteiCode As String, ByVal lotNo As String, Optional ByVal EQUIP_NO As String = "") As DataTable
        'szSql &= " sum(pkg_count - (ch_count + cn_count)) as totalVacantChipCnt, "
        'szSql &= " count(case when pkg_count <> (ch_count + cn_count) Then 1 End) as vacantPcbCnt, "
        'szSql &= " sum(pkg_count - ch_count) as totalCrossCnt, "
        'szSql &= " count(frame_id) as frameCnt, " 'フレーム数
        'szSql &= " NVL(sum(pkg_count-ch_count),0) as totalCrossCnt," 'X印数

        'szSql &= " and (CH_COUNT <> 0 OR CN_COUNT <> 0) "

        '<FRAME>
        Dim szSql As String = String.Empty
        If kouteiCode = PROC_CD_DBA Then
            szSql &= "select NVL(sum(pkg_count),0) as totalPkgCnt, " '総数
            szSql &= " count(case when (CH_COUNT <> 0 OR  CN_COUNT <> 0)  then 1 end) as frameCnt, " 'フレーム数
            szSql &= " NVL(sum(ch_count),0) as totalOkCnt, " '良品数
            szSql &= " NVL(sum(cn_count),0) as totalNgCnt, " '不良数
            szSql &= " sum((CASE WHEN (CH_COUNT <> 0) OR (CN_COUNT <> 0) THEN NVL(ck_count,0) END)) as totalEmptyCnt," '空チップ数
            szSql &= " NVL(NVL(sum(cn_count),0)-NVL(sum(case when CH_COUNT = 0 then pkg_count end),0) + NVL(sum(cm_count),0),0) as totalCrossCnt," 'X印数
            szSql &= " NVL(sum(cm_count),0) as totalDontCnt," '前工程未処理数
            szSql &= " count(case when pkg_count <> ch_count then 1 end) - count(case when CH_COUNT = 0 then 1 end) +count(case when (CH_COUNT=0 AND CN_COUNT>0 ) then 1 end)  as crossPcbCnt " 'X印枚数
            szSql &= "from tra.l_frame_info "
            szSql &= "where koutei_code = '" & kouteiCode & "' "
            szSql &= " and lot_no = '" & lotNo & "'"
        Else

            szSql &= "select NVL(sum(pkg_count),0) as totalPkgCnt, " '総数
            szSql &= " count(case when CH_COUNT <> 0 then 1 end) as frameCnt, " 'フレーム数
            szSql &= " NVL(sum(ch_count),0) as totalOkCnt, " '良品数
            szSql &= " NVL(sum(cn_count),0) as totalNgCnt, " '不良数
            szSql &= " sum((CASE WHEN CH_COUNT<> 0 THEN NVL(ck_count,0) END)) as totalEmptyCnt," '空チップ数
            szSql &= " NVL(NVL(sum(cn_count),0)-NVL(sum(case when CH_COUNT = 0 then pkg_count end),0) + NVL(sum(cm_count),0),0) as totalCrossCnt," 'X印数
            szSql &= " NVL(sum(cm_count),0) as totalDontCnt," '前工程未処理数
            szSql &= " count(case when pkg_count <> ch_count then 1 end) - count(case when CH_COUNT = 0 then 1 end) as crossPcbCnt " 'X印枚数
            If kouteiCode = PROC_CD_MARK Then
                szSql &= "from tra.l_frame_info_prog "
                szSql &= "where fe_koutei_code = '" & kouteiCode & "' and EQUIP_NO = '" & EQUIP_NO & "'"
            Else
                szSql &= "from tra.l_frame_info "
                szSql &= "where koutei_code = '" & kouteiCode & "' "
            End If
            szSql &= " and lot_no = '" & lotNo & "'"
        End If
        Dim dt As DataTable = DBacs.ExecuteSql(szSql, -1)

        If IsNothing(dt) OrElse dt(0)("totalPkgCnt") = "0" Then
            Return New DataTable
        End If

        'Map_Dataの追加：複数フレームの情報をまとめ、１列（MPA_DATA)に記入する
        szSql = String.Empty
        szSql &= "Select map_data "
        If kouteiCode = "A54" Then
            szSql &= "from tra.l_frame_info_prog "
            szSql &= "where fe_koutei_code = '" & kouteiCode & "'"
        Else
            szSql &= "from tra.l_frame_info "
            szSql &= "where koutei_code = '" & kouteiCode & "'"
        End If
        szSql &= " and lot_no = '" & lotNo & "'"
        szSql &= " and （CH_COUNT <> 0 OR CN_COUNT <> 0) "
        If kouteiCode = "A54" Then
            szSql &= " and UPPER(EQUIP_NAME) LIKE '%" & EQUIP_NO & "%'"
        End If
        Dim dt_Mapdata As DataTable = DBacs.ExecuteSql(szSql， -1)
        If IsNothing(dt_Mapdata) Then
            Return New DataTable
        Else
            Dim strMapdata As String = String.Empty
            dt.Columns.Add("map_data")
            For i = 0 To dt_Mapdata.Rows.Count - 1
                strMapdata += dt_Mapdata.Rows(i).Item(0).ToString()
            Next
            dt.Rows(0).Item("map_data") = strMapdata
        End If

        Return dt
    End Function

    Public Function getFrameCount(lotNo As String) As Integer
        'select count(Frame_ID) from tra.l_frame_info where lot_no ='ZVE291000V'
        Dim szSql As String = String.Empty
        Dim dtLot As DataTable
        szSql = ""
        szSql &= "select nvl(count(Frame_ID),0)  fraCount from tra.l_frame_info where lot_no ='" & lotNo & "'"

        dtLot = DBacs.ExecuteSql(szSql, -1)

        Return dtLot(0)(0)
    End Function

    Public Function getWBKaraFrameInfo(lotNo As String, equipNo As String) As DataTable
        'select count(Frame_ID) from tra.l_frame_info where lot_no ='ZVE291000V'
        Dim szSql As String = String.Empty
        Dim dtLot As DataTable
        szSql = ""
        szSql &= "select * from tra.l_frame_info where EQUIP_NO = ? AND CK_COUNT>0 AND lot_no =?"

        dtLot = DBacs.ExecuteSql(szSql, -1, equipNo, lotNo)

        Return dtLot
    End Function


    Public Function getFrameMapData(procCD As String, lotNo As String, EQUIP_NO As String) As String
        Dim szSql As String = String.Empty
        Dim strMapdata As String = ""
        'Map_Dataの追加：複数フレームの情報をまとめ、１列（MPA_DATA)に記入する
        szSql = String.Empty
        szSql &= "Select map_data "
        szSql &= "from tra.l_frame_info "
        szSql &= "where koutei_code = '" & procCD & "'"
        szSql &= " and lot_no = '" & lotNo & "'"
        szSql &= " and （CH_COUNT <> 0 OR CN_COUNT <> 0) "
        If procCD = "A54" Then
            szSql &= " and EQUIP_NO = '" & EQUIP_NO & "'"
        End If
        Dim dt_Mapdata As DataTable = DBacs.ExecuteSql(szSql， -1)
        If IsNothing(dt_Mapdata) Then
            strMapdata = ""
        Else

            For i = 0 To dt_Mapdata.Rows.Count - 1
                strMapdata += dt_Mapdata.Rows(i).Item(0).ToString()
            Next

        End If
        Return strMapdata
    End Function

    ''' <summary>
    ''' 不良情報を取得
    ''' </summary>
    ''' <param name="equip_type"></param>
    ''' <param name="category_code"></param>
    ''' <returns></returns>
    ''' <remarks></remarks>
    Public Function GetErrorInfo(ByVal equip_type As String, ByVal category_code() As String) As DataTable 'by xulei 2018/11/13 不良情報を追加
        Dim szSql As String = String.Empty
        szSql &= "select category_code,category_name as category_code_2"
        szSql &= " from tra.t_ng_category_info "
        szSql &= " where equip_type = " & equip_type
        szSql &= " and data_type = 4 "

        Dim strCategoryCode As String = "("
        For i = 0 To category_code.Count - 1
            strCategoryCode &= (System.Text.Encoding.Default.GetBytes(category_code(i)))(0).ToString
            strCategoryCode &= ","
        Next
        strCategoryCode = strCategoryCode.Substring(0, strCategoryCode.Length - 1) & ")"
        szSql &= " and category_code in " & strCategoryCode

        Dim dt As DataTable = DBacs.ExecuteSql(szSql, -1)
        If IsNothing(dt) Then
            Return New DataTable
        Else
            dt.Columns.Add("category_name")

            For i = 0 To dt.Rows.Count - 1
                Dim strError = dt.Rows(i).Item(1).ToString.Split(":")
                dt.Rows(i).Item(1) = strError(0)
                dt.Rows(i).Item(2) = strError(1)
            Next
            Return dt
        End If

    End Function 'by xulei 2018/11/13 不良情報を追加
    Public Function getInvNumByRackID(koutei_code As String, lot_no As String, rack_id As String) As Integer
        Dim szSql As String = String.Empty
        Dim invnum As Integer = 0
        szSql &= "SELECT SUM(CH_COUNT) AS TOTALOKCNT FROM TRA.L_FRAME_INFO "
        szSql &= "WHERE KOUTEI_CODE =? AND LOT_NO =? AND RACK_ID =?"
        Dim dt As DataTable = DBacs.ExecuteSql(szSql, -1, koutei_code, lot_no, rack_id)

        If dt.Rows.Count = 0 Then
            invnum = 0
        Else
            If IsDBNull(dt.Rows(0)("totalOkCnt")) Then
                invnum = 0
            Else
                invnum = CInt(dt.Rows(0)("totalOkCnt"))
            End If
        End If

        Return invnum
    End Function
    'ADD MIAO 2019/10/1 end

    Public Sub PutLog(ByVal szSender As String, ByVal szOperator As String, ByVal szMsg As String)
        Dim logRecord As frmStartCompleteBase.Log

        With logRecord
            .szEquipID = g_EQUIP_NO 'UPD 苗 2018/1/19 装置No.の対応
            .szProcCD = ""
            .szLotNo = ""
            .dteOpt = DateTime.Now
            .szOperator = szOperator
            .szControl = szSender.Replace(",", "")
            .szMsg = szMsg
        End With

        'PutFileLog(logRecord)
        PutDbLog(logRecord)
    End Sub
    Private Function PutDbLog(ByVal logRecord As frmStartCompleteBase.Log) As Integer
        Dim a As New clsIcTagLog
        Return a.PutDbLog(logRecord)
    End Function
End Class
